IF EXISTS(SELECT name FROM sys.databases
     WHERE name = 'loghog')
     DROP DATABASE loghog;

CREATE DATABASE loghog
GO

IF (OBJECT_ID('Groups')IS NOT NULL)
DROP TABLE Groups;

CREATE TABLE loghog..Groups
(ID int IDENTITY(1,1) not null primary key,
Name nvarchar(60),
CanRead char(1),
CanAdd char(1),
CanExport char(1),
CanImport char(1),
CanAddField char(1),
CanDelField char(1),
CanAddUsers char(1),
CanDelUsers char(1),
CanCreateGroups char(1),
CanDelGroups char(1),
CanCreateJournals char(1),
CanDelJournals char(1));

IF (OBJECT_ID('Type')IS NOT NULL)
DROP TABLE Type;

CREATE TABLE loghog..Type
(ID int IDENTITY(1,1) not null primary key,
Name nvarchar(30),
Description nvarchar(MAX));

IF (OBJECT_ID('Users')IS NOT NULL)
DROP TABLE Users;

CREATE TABLE loghog..Users
(ID int IDENTITY(1,1) not null primary key,
Name nvarchar(60),
Password nvarchar(20),
GroupsID int foreign key references Groups(ID));

IF (OBJECT_ID('Journal')IS NOT NULL)
DROP TABLE Journal;

CREATE TABLE loghog..Journal
(ID int IDENTITY(1,1) not null primary key,
Name nvarchar(80),
UserID int foreign key references Users(ID));

IF (OBJECT_ID('Journal_Users')IS NOT NULL)
DROP TABLE Journal_Users;

CREATE TABLE loghog..Journal_Users
(ID int IDENTITY(1,1) not null primary key,
JournalID int foreign key references Journal(ID),
UsersID int foreign key references Users(ID));

IF (OBJECT_ID('Journal_Groups')IS NOT NULL)
DROP TABLE Journal_Groups;

CREATE TABLE loghog..Journal_Groups
(ID int IDENTITY(1,1) not null primary key,
JournalID int foreign key references Journal(ID),
GroupsID int foreign key references Groups(ID));

IF (OBJECT_ID('Field')IS NOT NULL)
DROP TABLE Field;

CREATE TABLE loghog..Field
(ID int IDENTITY(1,1) not null primary key,
Name nvarchar(80),
Description nvarchar(MAX),
DataCreation datetime DEFAULT GETDATE(),
AgreementAuthor nvarchar(60),
TypeID int foreign key references Type(ID));

IF (OBJECT_ID('Journal_Field')IS NOT NULL)
DROP TABLE Journal_Field;

CREATE TABLE loghog..Journal_Field
(ID int IDENTITY(1,1) not null primary key,
JournalID int foreign key references Journal(ID),
FieldID int foreign key references Field(ID));

IF (OBJECT_ID('Value')IS NOT NULL)
DROP TABLE Value;

CREATE TABLE loghog..Value
(ID int IDENTITY(1,1) not null primary key,
BLOB sql_variant,
Time datetime DEFAULT GETDATE(),
JournalID int foreign key references Journal(ID));

IF (OBJECT_ID('Field_Value')IS NOT NULL)
DROP TABLE Field_Value;

CREATE TABLE loghog..Field_Value
(ID int IDENTITY(1,1) not null primary key,
FieldID int foreign key references Field(ID),
ValueID int foreign key references Value(ID));



